package com.jiepos.mpos.biz.transaction.service;

import java.io.File;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.dao.StlCustomDao;
import com.jiepos.mpos.common.entity.App_Stl_CustomEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.StringUtils;

import oracle.jdbc.OracleTypes;


/**
 * 会员提现清算报表业务类
 * 
 * @ProjectName: [mpos1]
 * @Package com.jiepos.mpos.biz.transaction.service
 * @author 肖志伟
 * @version 1.0
 * @since 2017年11月7日 上午9:26:54
 * @history（历次修订内容、修订人、修订时间等） @UpdateRemark [说明本次修改内容]
 * @Copyright (c) 2017 江苏杰博实信息技术有限公司
 */
@Service
public class StlCustomService extends BaseService {
	
	@Value("${stlcustomer.file.save.path}")
	private String stlcustomer;
	@Value("${pay.account.no}")
	private String payAccountNo;

	@Autowired
	private StlCustomDao stlCustomDao;

	/**
	 * 
	 * @param request
	 * @param pagination
	 * @param appStlCustomEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_name
	 * @param custom_login_id
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map getStlCustomList(HttpServletRequest request, Pagination pagination,
			App_Stl_CustomEntity appStlCustomEntity, String stl_dt_start, String stl_dt_end, String custom_name,
			String custom_login_id) {
		JSONObject common = this.getQuerySqlAndParams(appStlCustomEntity, stl_dt_start, stl_dt_end, custom_name, custom_login_id);
		pagination.setQueryString(common.getString("sql"));
		pagination = super.getCustomAuthSql(request, pagination, "t2", null);
		pagination.queryString = pagination.queryString + " order by t1.stl_dt desc";
		return stlCustomDao.getPaginationMap(pagination, "t1", common.getObject("params", List.class));
	}

	/**
	 * 导出数据
	 * 
	 * @param fileName
	 * @param request
	 * @param response
	 * @param pagination
	 * @param appStlCustomEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_name
	 * @param custom_login_id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String exportIStlCustomLists(String fileName, HttpServletRequest request, HttpServletResponse response,
			Pagination pagination, App_Stl_CustomEntity appStlCustomEntity, String stl_dt_start, String stl_dt_end,
			String custom_name, String custom_login_id) {
		JSONObject common = this.getQuerySqlAndParams(appStlCustomEntity, stl_dt_start, stl_dt_end, custom_name, custom_login_id);
		String sql = super.getCustomAuthSql(request, common.getString("sql"), "t2", null);
		sql = sql + " order by t1.stl_dt desc";
		List<Map> resultList = stlCustomDao.queryForList(sql, common.getObject("params", List.class));
		List<String> headList = new ArrayList<String>();
		headList.add("清算日期");
		headList.add("会员编号");
		headList.add("会员名");
		headList.add("总行行号");
		headList.add("总行名称");
		headList.add("清算资金开户行号");
		headList.add("清算资金帐号");
		headList.add("清算资金帐户名称");
		headList.add("清算资金开户行名称");
		headList.add("证件号");
		headList.add("清算金额");
		headList.add("手续费");
		return ExcelUtil.writeExcel(fileName, "会员提现清算报表", headList, resultList);
	}

	/**
	 * 查询导出数据
	 * 
	 * @param request
	 * @param response
	 * @param appStlCustomEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_name
	 * @param custom_login_id
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	private JSONObject getQuerySqlAndParams(App_Stl_CustomEntity appStlCustomEntity, String stl_dt_start, String stl_dt_end, String custom_name,
			String custom_login_id) {
		StringBuffer sqlSb = new StringBuffer();
		List list = new ArrayList();
		sqlSb.append("select '' as uuid, ");
		sqlSb.append("TO_CHAR(TO_DATE(t1.stl_dt, 'YYYY-MM-DD'), 'yyyy-mm-dd') as stl_dt, ");
		sqlSb.append("t2.custom_no, ");
		sqlSb.append("CONCAT(RPAD (SUBSTR (t2.login_id, 1, 3),LENGTH (t2.login_id) - 4,'*'),SUBSTR (t2.login_id,LENGTH (t2.login_id) - 3,4))||'-'||t2.name as custom_name, ");
		sqlSb.append("t1.super_bank_no, ");
		sqlSb.append("t1.super_bank_nm, ");
		sqlSb.append("t1.settle_bank_no, ");
		sqlSb.append("t1.settle_acct, ");
		sqlSb.append("t1.acct_nm, ");
		sqlSb.append("t1.bank_nm, ");
		sqlSb.append("t1.certificate_no, ");
		sqlSb.append("to_char(t1.stl_amt,'fm9999999990.00') as stl_amt, ");
		sqlSb.append("to_char(t1.midfee,'fm9999999990.00') as midfee ");
		sqlSb.append(" from ");
		sqlSb.append(Table.T_APP_STL_CUSTOM + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_CUSTOM_INF + " t2 ");
		sqlSb.append("on t1.custom_no = t2.custom_no ");
		sqlSb.append("where 1=1 ");
		if (!StringUtils.isBlank(stl_dt_start)) {
			Date date = DateUtil.strToDate(stl_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		} else {
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.getAddDaysDate(new Date(), -31));
		}
		if (!StringUtils.isBlank(stl_dt_end)) {
			Date date = DateUtil.strToDate(stl_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append("and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		} else {
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(new Date(), "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(appStlCustomEntity.getCustom_no())) {
			sqlSb.append("and t1.custom_no like ? ");
			list.add("%" + appStlCustomEntity.getCustom_no() + "%");
		}
		if (!StringUtils.isBlank(custom_name)) {
			sqlSb.append("and t2.name like ? ");
			list.add("%" + custom_name + "%");
		}
		if (!StringUtils.isBlank(custom_login_id)) {
			sqlSb.append("and t2.login_id like ? ");
			list.add("%" + custom_login_id + "%");
		}
		JSONObject common = new JSONObject();
		common.put("sql", sqlSb.toString());
		common.put("params", list);
		return common;
	}
	
	public Map<String, Object> getPinganReport(HttpServletRequest request, Pagination pagination){
		Map<String, Object> pages = new HashMap<>();
		/*获取所有的文件*/
		File[] files = new File(stlcustomer).listFiles();
		List<Map<String, String>> datas = new ArrayList<>(); 
		/*遍历文件，组装列表数据*/
		if(files != null && files.length > 0){
			int index = (pagination.getPage() - 1) * pagination.getRows();
			int number = pagination.getPage() * pagination.getRows();
			if(number > files.length){
				number = files.length;
			}
			if(index < number){
				for(;index < number;index++){
					Map<String, String> data = new LinkedHashMap<>();
					data.put("fileName", files[index].getName());
					data.put("filePath", files[index].getPath());
					datas.add(data);
				}
			}
		}
		pages.put("total", files == null ? 0 : files.length);
		pages.put("rows", datas);
		return pages;
	}
	
	/**
	 * 生成提现打款报表
	 */
	public void createPingAnReport(){
		if(!stlCustomDao.isWorkDay()){
			return;
		}
		createPingAnReport(null, null);
	}
	
	/**
	 * 生成提现打款报表
	 * @param beginDate 开始日期
	 * @param endDate 结束日期
	 */
	public void createPingAnReport(String beginDate, String endDate){
		/*获取打款数据*/
		List<Map<String, Object>> datas = queryStlCustomerList(beginDate, endDate);
		createPingAnReportExcel(datas);
	}
	
	/**
	 * 获取清算打款数据
	 * @param beginDate 开始日期
	 * @param endDate 结束日期
	 * @return
	 */
	private List<Map<String, Object>> queryStlCustomerList(String beginDate, String endDate){
		/*获取查询日期*/
		if(StringUtils.isBlank(beginDate) || StringUtils.isBlank(endDate)){
			Map<String, Object> date = stlCustomDao.queryProcedure("P_BEGIN_END_DATE", new int[]{OracleTypes.VARCHAR, OracleTypes.VARCHAR}, new Object[]{"YYYYMMDD", null});
			beginDate = (String) date.get("3");
			endDate = (String) date.get("4");
		}
		/*组装sql*/
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT NULL TRACE_NO,")
		.append("       ? PAY_NO,")
		.append("       SETTLE_ACCT,")
		.append("       ACCT_NM,")
		.append("       NULL ACCT_PRO,")
		.append("       NULL ACCT_CITY,")
		.append("       NULL BANK_NM,")
		.append("       TO_CHAR(SUM(STL_AMT), 'FM999999999990.00') STL_AMT,")
		.append("       '分润' REMARK,")
		.append("       SETTLE_BANK_NO,")
		.append("       NULL PHONE")
		.append("  FROM T_APP_STL_CUSTOM")
		.append(" WHERE STL_DT >= ?")
		.append("   AND STL_DT < ?")
		.append(" GROUP BY SETTLE_ACCT, ACCT_NM, SETTLE_BANK_NO")
		.append(" ORDER BY STL_AMT");
		return stlCustomDao.queryForListMap(sql.toString(), new Object[]{payAccountNo, beginDate, endDate});
	}
	
	/**
	 * 生成excel打款文件
	 * @param datas
	 */
	private void createPingAnReportExcel(List<Map<String, Object>> datas){
		if(datas == null || datas.isEmpty()){
			return;
		}
		/*总数分表记录，每表只能记录1万条*/
		/*分表数量*/
		int i;
		if(datas.size() % 10000 == 0){
			i = datas.size() / 10000;
		}else{
			i = datas.size() / 10000 + 1;
		}
		/*生成流水号*/
		Long traceNo = Long.valueOf(DateUtil.getCurrDateTimeStr() + "00001");
		int k = 1;
		for(int j=1; j<=i; j++){
			List<Map<String, Object>> excelDatas = new ArrayList<>();
			for(;k<=datas.size();k++){
				Map<String, Object> data = datas.get(k-1);
				data.put("TRACE_NO", traceNo++);
				excelDatas.add(data);
				if(k%10000 == 0){
					k++;
					break;
				}
			}
			ExcelUtil.writeExcel(creatFileName(j), "数据", excelDatas);
		}
	}
	
	private String creatFileName(int page){
		StringBuffer fileName = new StringBuffer(stlcustomer);
		fileName.append("req_").append(DateUtil.getCurrDateStr()).append("_");
		for(int i=0;i<4-String.valueOf(page).length();i++){
			fileName.append("0");
		}
		fileName.append(page).append(".xls");
		return fileName.toString();
	}

}
